USE abyx_svcm  
GO

DECLARE @objName VARCHAR(MAX)
DECLARE @genMode VARCHAR(MAX)

--SELECT name FROM sys.objects where type='U' and SUBSTRING(name,1,3)<>'sys' Order by name

SET @objName = 'SVC_ServiceOrders'
SET @genMode = 'CREATE' --Set to CREATE or ALTER


DECLARE @objId INT
SET @objId=object_id(@objName)
DECLARE	@dbname	SYSNAME,@no VARCHAR(MAX), @yes VARCHAR(MAX), @none VARCHAR(MAX)
DECLARE @spDel NVARCHAR(MAX)
DECLARE @spGet NVARCHAR(MAX)
DECLARE @spIns NVARCHAR(MAX)
DECLARE @spUp NVARCHAR(MAX)
DECLARE @allsps NVARCHAR(MAX)
DECLARE @colList VARCHAR(MAX)
DECLARE @upColList VARCHAR(MAX)
DECLARE @colListNames VARCHAR(MAX)
SET @colList=''
SET @colListNames=''
SET @upColList=''
SET @allsps=''

DECLARE @prColName VARCHAR(MAX)
DECLARE @prColType VARCHAR(MAX)

--SET PK Column (Assumes the first column is the PK)
SELECT
	@prColName= [name],
	@prColType=type_name(user_type_id)
FROM sys.all_columns WHERE object_id = @objid AND column_id=1

--SET all column list
SELECT
	@colList = @colList + CHAR(9) +
						(CASE column_id
							WHEN 1 THEN ' @'
							ELSE ',@'
						END)
						+ [Name] + ' ' + 
						CASE type_name(user_type_id) 
							WHEN ('VARCHAR') THEN type_name(user_type_id) + '(' + replace(convert(VARCHAR,convert(INT, max_length)),'-1','MAX') +')'
							WHEN ('NVARCHAR') THEN type_name(user_type_id) + '(MAX)'
							WHEN 'CHAR' THEN type_name(user_type_id) + '(' + convert(VARCHAR,convert(INT, max_length)) +')'
							WHEN 'DECIMAL' THEN type_name(user_type_id) + '(18,5)'
							ELSE type_name(user_type_id) 
						END
						+ CHAR(13),
	@colListNames = @colListNames + 
						(CASE column_id
							WHEN 1 THEN ' @'
							ELSE ',@'
						END)
						+ [Name] 
FROM sys.all_columns WHERE object_id = @objid AND is_computed<>1 AND [Name] <> 'deleted'


SET @spdel ='
--[SP_' + @objName + 'Delete]
IF  EXISTS (SELECT * 
			FROM dbo.sysobjects 
			WHERE id = OBJECT_ID(N''[SP_' + @objName + 'Delete]'') 
			AND OBJECTPROPERTY(id,N''IsProcedure'') = 1)
	DROP PROCEDURE [SP_' + @objName + 'Delete]
GO
/****** Object:  StoredProcedure [dbo].[SP_' + @objName + 'Delete]    Script Date: ' + SUBSTRING(CONVERT(VARCHAR,GETDATE(),21),1,16) + ' ******/
'
 + @genMode + ' PROCEDURE [dbo].[SP_' + @objName + 'Delete]
	@' + @prColName + ' ' + @prColType +'
AS
BEGIN
	DELETE FROM [' + @objName + '] WHERE [' + @prColName + ']=@' + @prColName + '
	RETURN @@error
END
Go
'
SET @spGet='
--[SP_' + @objName + 'Get]
IF  EXISTS (SELECT * 
			FROM dbo.sysobjects 
			WHERE id = OBJECT_ID(N''[SP_' + @objName + 'Get]'') 
			AND OBJECTPROPERTY(id,N''IsProcedure'') = 1)
	DROP PROCEDURE [SP_' + @objName + 'Get]
GO
/****** Object:  StoredProcedure [dbo].[SP_' + @objName + 'Get]    Script Date: ' + SUBSTRING(CONVERT(VARCHAR,GETDATE(),21),1,16) + ' ******/
' + @genMode + ' PROCEDURE [dbo].[SP_' + @objName + 'Get]
	 @' + @prColName + ' ' + @prColType + '
AS
BEGIN
	SELECT * FROM [' + @objName + '] WHERE [' + @prColName + ']=@' + @prColName + '
	RETURN @@error
END
GO
'

SET @spIns='
--[SP_' + @objName + 'Insert]
IF  EXISTS (SELECT * 
			FROM dbo.sysobjects 
			WHERE id = OBJECT_ID(N''[SP_' + @objName + 'Insert]'') 
			AND OBJECTPROPERTY(id,N''IsProcedure'') = 1)
	DROP PROCEDURE [SP_' + @objName + 'Insert]
GO
/****** Object:  StoredProcedure [dbo].[SP_' + @objName + 'Insert]    Script Date: ' + SUBSTRING(CONVERT(VARCHAR,GETDATE(),21),1,16) + ' ******/
' + @genMode + ' PROCEDURE [SP_' + @objName + 'Insert]
' + @colList + 'AS
BEGIN
	INSERT INTO [' + @objName + ']
		 VALUES(' + @colListNames + ')
	RETURN @@error
END
GO
'

--SET update columnlist
declare @firstId int
SELECT TOP 1 
	@firstId=column_id
FROM sys.all_columns WHERE object_id = @objid AND Name <> 'deleted' AND column_id <> 1 AND is_computed<>1
ORDER BY column_id
SELECT
	@upColList = @upColList + CHAR(9) + CHAR(9) + CHAR(9) +
						(CASE column_id
							WHEN @firstId THEN ' [' + name + '] = @' + name
							ELSE ',[' + name + '] = @' + name
						END) + CHAR(13)
FROM sys.all_columns WHERE object_id = @objid AND Name <> 'deleted' AND column_id <> 1 AND is_computed<>1


SET @spUp='
--[SP_' + @objName + 'Update]
IF  EXISTS (SELECT * 
			FROM dbo.sysobjects 
			WHERE id = OBJECT_ID(N''[SP_' + @objName + 'Update]'') 
			AND OBJECTPROPERTY(id,N''IsProcedure'') = 1)
	DROP PROCEDURE [SP_' + @objName + 'Update]
GO
/****** Object:  StoredProcedure [dbo].[SP_' + @objName + 'Update]    Script Date: ' + SUBSTRING(CONVERT(VARCHAR,GETDATE(),21),1,16) + ' ******/
' + @genMode + ' PROCEDURE [dbo].[SP_' + @objName + 'Update]
' + @colList + 'AS
BEGIN
	UPDATE [' + @objName + ']
	   SET
' + @upColList + '	WHERE [' + @prColName + '] = @' + @prColName + '
	RETURN @@error
END
GO
'

SET @allsps = 'Use [abyx_svcm]
GO
'
+ @spget + CHAR(13)
+ @spins + CHAR(13)
+ @spup + CHAR(13)
+ @spdel + CHAR(13)


--DO LONG PRINTING (8000 character limit problem with SSMS)
SET NOCOUNT ON

set @allsps = rtrim( @allsps )

declare @cr char(1), @lf char(1)
set @cr = char(13)
set @lf = char(10)

declare @len int, @cr_index int, @lf_index int, @crlf_index int, @has_cr_and_lf bit, @left nvarchar(4000), @reverse nvarchar(4000)
set @len = 4000

while ( len( @allsps ) > @len )
begin
   set @left = left( @allsps, @len )
   set @reverse = reverse( @left )
   set @cr_index = @len - charindex( @cr, @reverse ) + 1
   set @lf_index = @len - charindex( @lf, @reverse ) + 1
   set @crlf_index = case when @cr_index < @lf_index then @cr_index else @lf_index end
   set @has_cr_and_lf = case when @cr_index < @len and @lf_index < @len then 1 else 0 end
   print left( @allsps, @crlf_index - 1 )
   set @allsps = right( @allsps, len( @allsps ) - @crlf_index - @has_cr_and_lf )
end

print @allsps


--EXECUTE sp_executesql @allsps;












